#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
with
tmp1_table as
(
select
	num,
	null percent1,
	concat_ws('-',name1,content) as content,
	'毕业生就业情况' as name,
	cast(current_date() as string) operate_time
from (
	select
		name1,
		content,
		count(name) as num
	from (
		select
			name,
			case when enterprise_recruitment is not null then '企业招聘'
				when startup_code is not null then '自主创业'
				when company_work is not null then '单位就业'
				when autonomy_work is not null then '自主就业'
				when special_position_work is not null then '特殊岗位就业'
				when unemploy_type is not null then '未就业'
				else '其他' end as content,
			case when date_format(edu_time,'yyyy') = cast(date_format(current_date(),'yyyy') as string) then '应届毕业生'
				else '往届毕业生' end as name1
		from cy_database.dws_college_graduate_output
		)a
	group by name1,content
	)aa
),




tmp2_table as
(
select
	num,
	null percent1,
	concat_ws('-',name1,content) as content,
	'就业领域' as name,
	cast(current_date() as string) operate_time
from (
	select
		name1,
		content,
		count(name) as num
	from (
		select
			name,
			case when date_format(edu_time,'yyyy') = cast(date_format(current_date(),'yyyy') as string) then '应届毕业生'
				else '往届毕业生' end as name1,
			case when business_code = '900001' then	'居民服务业'
				when business_code = '900002' then	'银行业'
				when business_code = '900003' then	'农林牧渔业'
				when business_code = '900004' then	'中国共产党机关'
				when business_code = '900005' then	'国际组织'
				when business_code = '900006' then	'住宿业'
				when business_code = '900007' then	'房屋与土木工程建筑业'
				when business_code = '900008' then	'电力、热力的生产和供应业'
				when business_code = '900009' then	'房地产业'
				when business_code = '900010' then	'农副食品加工业'
				when business_code = '900011' then	'研究与试验发展'
				when business_code = '900012' then	'教育'
				when business_code = '900013' then	'卫生'
				when business_code = '900014' then	'租赁业'
				when business_code = '900015' then	'批发业'
				when business_code = '900016' then	'新闻出版业'
				when business_code = '900017' then	'铁路运输业'
				when business_code = '900018' then	'煤炭开采与洗选业'
				when business_code = '900019' then	'水利管理业'
				when business_code = '900020' then	'电信和其他信息传输服务业'
				when business_code = '900021' then	'农业'
				when business_code = '900022' then	'林业'
				when business_code = '900023' then	'零售业'
				when business_code = '900024' then	'食品制造业'
				when business_code = '900025' then	'石油和天然气开采业'
				when business_code = '900026' then	'采矿业'
				when business_code = '900027' then	'社会保障业'
				when business_code = '900028' then	'其他服务业'
				when business_code = '900029' then	'环境管理业'
				when business_code = '900030' then	'专业技术服务业'
				when business_code = '900031' then	'商务服务业'
				when business_code = '900032' then	'餐饮业'
				when business_code = '900033' then	'道路运输业'
				when business_code = '900034' then	'建筑安装业'
				when business_code = '900035' then	'计算机服务业'
				when business_code = '900036' then	'证券业'
				when business_code = '900037' then	'燃气生产和供应业'
				when business_code = '900038' then	'国家机构'
				when business_code = '900039' then	'广播、电视、电影和音像业'
				when business_code = '900040' then	'公共设施管理业'
				when business_code = '900041' then	'水的生产和供应业'
				when business_code = '900042' then	'制造业'
				when business_code = '900043' then	'科技交流和推广服务业'
				when business_code = '900044' then	'黑色金属矿采选业'
				when business_code = '900045' then	'建筑装饰业'
				when business_code = '900046' then	'畜牧业'
				when business_code = '900047' then	'城市公共交通业'
				when business_code = '900048' then	'人民政协和民主党派'
				when business_code = '900049' then	'社会福利业'
				when business_code = '900050' then	'文化艺术业'
				when business_code = '900051' then	'软件业'
				when business_code = '900052' then	'保险业'
				when business_code = '900053' then	'饮料制造业'
				when business_code = '900054' then	'地质勘查业'
				when business_code = '900055' then	'渔业'
				when business_code = '900056' then	'有色金属矿采选业'
				when business_code = '900057' then	'烟草制品业'
				when business_code = '900058' then	'电力、煤气及水生产和供应'
				when business_code = '900059' then	'其他金融活动'
				when business_code = '900060' then	'体育'
				when business_code = '900061' then	'群众团体、社会团体和宗教组织'
				when business_code = '900062' then	'其他建筑业'
				when business_code = '900063' then	'水上运输业'
				when business_code = '900064' then	'基层群众自治组织'
				when business_code = '900065' then	'娱乐业'
				when business_code = '900066' then	'建筑业'
				when business_code = '900067' then	'航空运输业'
				when business_code = '900068' then	'农、林、牧、渔服务业'
				when business_code = '900069' then	'非金属矿采选业'
				when business_code = '900070' then	'纺织业'
				when business_code = '900071' then	'其他采矿业'
				when business_code = '900072' then	'纺织服装、鞋、帽制造业'
				when business_code = '900073' then	'管道运输业'
				when business_code = '900074' then	'交通运输、仓储和邮政业'
				when business_code = '900075' then	'信息传输、计算机服务和软件业'
				when business_code = '900076' then	'皮革、毛皮、羽毛（绒及其制品业）'
				when business_code = '900077' then	'装卸搬运和其他运输服务业'
				when business_code = '900078' then	'木材加工及木、竹、藤、棕、草制品业'
				when business_code = '900079' then	'仓储业'
				when business_code = '900080' then	'批发和零售业'
				when business_code = '900081' then	'邮政业'
				when business_code = '900082' then	'住宿和餐饮业'
				when business_code = '900083' then	'家具制造业'
				when business_code = '900084' then	'金融业'
				when business_code = '900085' then	'造纸及纸制品业'
				when business_code = '900086' then	'印刷业和记录媒介的复制'
				when business_code = '900087' then	'租赁和商务服务业'
				when business_code = '900088' then	'文教体育用品制造业'
				when business_code = '900089' then	'石油加工、炼焦及核燃料加工业'
				when business_code = '900090' then	'科学研究、技术服务和地质勘查业'
				when business_code = '900091' then	'水利、环境和公共设施管理业'
				when business_code = '900092' then	'化学原料及化学制品制造业'
				when business_code = '900093' then	'居民服务和其他服务业'
				when business_code = '900094' then	'医药制造业'
				when business_code = '900095' then	'化学纤维制造业'
				when business_code = '900096' then	'卫生、社会保障和社会福利业'
				when business_code = '900097' then	'橡胶制品业'
				when business_code = '900098' then	'文化、体育和娱乐业'
				when business_code = '900099' then	'塑料制品业'
				when business_code = '900100' then	'非金属矿物制品业'
				when business_code = '900101' then	'公共管理和社会组织'
				when business_code = '900102' then	'黑色金属冶炼及压延加工业'
				when business_code = '900103' then	'有色金属冶炼及压延加工业'
				when business_code = '900104' then	'金属制品业'
				when business_code = '900105' then	'通用设备制造业'
				when business_code = '900106' then	'专用设备制造业'
				when business_code = '900107' then	'交通运输设备制造业'
				when business_code = '900108' then	'电气机械及器材制造业'
				when business_code = '900109' then	'通信设备、计算机及其他电子设备制造业'
				when business_code = '900110' then	'仪器仪表及文化、办公用机械制造业'
				when business_code = '900111' then	'工艺品及其他制造业'
				when business_code = '900112' then	'废弃资源和废旧材料回收加工业'
				end as content
		from (
			select
				cgo.name,
				cgo.edu_time,
				pwd.business_code
			from cy_database.dws_college_graduate_output cgo
			join cy_database.dws_person_work_detail pwd on cgo.name = pwd.name
			)a
		)aa
	group by name1,content
	)aaa
),


tmp3_table as
(

select
	num,
	cast((num/amount) as decimal(10,5)) as percent1,
	'高校毕业生就业人数' content,
	'当界毕业生就业情况' name,
	cast(current_date() as string) as operate_time
from (
	select
		name,
		flag,
		sum(flag) over() as num,
		count(flag) over() as amount
	from (
		select
			name,
			case when enterprise_recruitment is not null then 1
				when startup_code is not null then 1
				when company_work is not null then 1
				when autonomy_work is not null then 1
				when special_position_work is not null then 1
				when work_other is not null then 0
				end as flag
		from cy_database.dws_college_graduate_output
		)a
	limit 1
	)aa
union all
select
	count(1) as num,
	null percent1,
	'应届毕业生总人数' content,
	'当界毕业生就业情况' name,
	cast(current_date() as string) as operate_time
from cy_database.dws_college_graduate_output

),



tmp4_table as 每年毕业生就业情况 没数据支持
(

select
	num,
	null percent1,
	'本地就业' content,
	'每年毕业生就业情况' name,
	df_time operate_time
from (
	select
		df_time,
		count(name) as num
	from (
		select
			name,
			date_format(edu_time,'yyyy') as df_time
		from cy_database.dws_college_graduate_output
		where working_area = '1'
		)a
	group by df_time
	)aa

union all
select
	num,
	null percent1,
	'外地就业' content,
	'每年毕业生就业情况' name,
	df_time operate_time
from (
	select
		df_time,
		count(name) as num
	from (
		select
			name,
			date_format(edu_time,'yyyy') as df_time
		from cy_database.dws_college_graduate_output
		where working_area = '0'
		)a
	group by df_time
	)aa

union all
select
	num,
	null percent1,
	'未就业' content,
	'每年毕业生就业情况' name,
	df_time operate_time
from (
	select
		df_time,
		count(name) as num
	from (
		select
			name,
			date_format(edu_time,'yyyy') as df_time
		from cy_database.dws_college_graduate_output
		where work_other is not null and work_other !='null'
		)a
	group by df_time
	)aa
),


tmp5_table as 见习毕业生留用情况 没数据支持
(

select
	400 num,
	null percent1,
	'国有企业-见习' content,
	'见习毕业生留用情况' name,
	cast(current_date() as string) operate_time
union all
select
	500 num,
	null percent1,
	'股份制企业-见习' content,
	'见习毕业生留用情况' name,
	cast(current_date() as string) operate_time
union all
select
	300 num,
	null percent1,
	'私营企业-见习' content,
	'见习毕业生留用情况' name,
	cast(current_date() as string) operate_time
union all
select
	500 num,
	null percent1,
	'特殊企业-见习' content,
	'见习毕业生留用情况' name,
	cast(current_date() as string) operate_time
union all
select
	400 num,
	null percent1,
	'其他企业-见习' content,
	'见习毕业生留用情况' name,
	cast(current_date() as string) operate_time

union all
select
	400 num,
	null percent1,
	'国有企业-留用' content,
	'见习毕业生留用情况' name,
	cast(current_date() as string) operate_time
union all
select
	700 num,
	null percent1,
	'股份制企业-留用' content,
	'见习毕业生留用情况' name,
	cast(current_date() as string) operate_time
union all
select
	200 num,
	null percent1,
	'私营企业-留用' content,
	'见习毕业生留用情况' name,
	cast(current_date() as string) operate_time
union all
select
	300 num,
	null percent1,
	'特殊企业-留用' content,
	'见习毕业生留用情况' name,
	cast(current_date() as string) operate_time
union all
select
	200 num,
	null percent1,
	'其他企业-留用' content,
	'见习毕业生留用情况' name,
	cast(current_date() as string) operate_time
),




tmp6_table as 高校毕业生外出务工 没数据支持
(

select
	600 num,
	null percent1,
	'上海' content,
	'高校毕业生外出务工' name,
	cast(current_date() as string) operate_time
union all
select
	500 num,
	null percent1,
	'河南' content,
	'高校毕业生外出务工' name,
	cast(current_date() as string) operate_time
union all
select
	800 num,
	null percent1,
	'广东' content,
	'高校毕业生外出务工' name,
	cast(current_date() as string) operate_time

union all
select
	500 num,
	null percent1,
	'江苏' content,
	'高校毕业生外出务工' name,
	cast(current_date() as string) operate_time

),


tmp7_table as
(
select
	content,
	count(name) as num,
	null percent1,
	'毕业生去向意向-本科' name,
	cast(current_date() as string) as operate_time
from(
	select
		name,
		case when keep_study = '1' then '升学'
			when enterprise_recruitment is not null then '就业'
			when autonomy_work is not null then '就业'
			when company_work is not null then '就业'
			when special_position_work is not null then '就业'
			when unemploy_type is not null then '未就业'
			end as content
	from cy_database.dws_college_graduate_output
	where education = '5'
	)a
group by content

union all
select
	content,
	count(name) as num,
	null percent1,
	'毕业生去向意向-硕士' name,
	cast(current_date() as string) as operate_time
from(
	select
		name,
		case when keep_study = '1' then '升学'
			when enterprise_recruitment is not null then '就业'
			when autonomy_work is not null then '就业'
			when company_work is not null then '就业'
			when special_position_work is not null then '就业'
			when unemploy_type is not null then '未就业'
			end as content
	from cy_database.dws_college_graduate_output
	where education = '6'
	)a
group by content
)



insert overwrite table cy_database.ads_big_screen_result_gaoxiao
select num,percent1,content,name,operate_time from tmp1_table

union all
select num,percent1,content,name,operate_time from tmp2_table

union all
select num,percent1,content,name,operate_time from tmp3_table

union all
select num,percent1,content,name,operate_time from tmp4_table

union all
select num,percent1,content,name,operate_time from tmp5_table

union all
select num,percent1,content,name,operate_time from tmp6_table

union all
select num,percent1,content,name,operate_time from tmp7_table
;



"

$hive -e "$sql"